package zy.dao.shop.deposit.impl;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.shop.deposit.DepositDAO;
import zy.entity.sell.deposit.T_Sell_Deposit;
import zy.entity.sell.deposit.T_Sell_DepositList;
import zy.util.CommonUtil;
import zy.util.DateUtil;
import zy.util.StringUtil;

@Repository
public class DepositDAOImpl extends BaseDaoImpl implements DepositDAO {
	@Override
	public Integer count(Map<String, Object> param) {
		Object begindate = param.get("begindate");
		Object enddate = param.get("enddate");
		Object sdl_state = param.get("sdl_state");
		Object sdl_shop_code = param.get("sdl_shop_code");
		Object pd_no = param.get("pd_no");
		Object sdl_number = param.get("sdl_number");
//		Object shop_code = param.get(CommonUtil.SHOP_CODE);
		Object shop_type = param.get(CommonUtil.SHOP_TYPE);
		StringBuffer sql = new StringBuffer("");
		sql.append(" select count(1)");
		sql.append(" from t_sell_depositlist t ");
		sql.append(" JOIN t_base_product pd ON pd.pd_code=t.sdl_pd_code AND pd.companyid=t.companyid ");
		sql.append(" JOIN t_sell_deposit sd ON sd.sd_number=t.sdl_number AND sd.companyid=t.companyid ");
		sql.append(" JOIN t_base_shop sp ON sp.sp_code=t.sdl_shop_code AND sp.companyid=t.companyid ");
		sql.append(" where 1 = 1");
		if (null != sdl_shop_code && !"".equals(sdl_shop_code)) {
			sql.append(" and  t.sdl_shop_code = :sdl_shop_code");
		}
		if (null != sdl_state && !"".equals(sdl_state)) {
			sql.append(" and  t.sdl_state = :sdl_state");
		}
		if (null != sdl_number && !"".equals(sdl_number)) {
			sql.append(" and  t.sdl_number = :sdl_number");
		}
		if (null != pd_no && !"".equals(pd_no)) {
			sql.append(" and  pd.pd_no = :pd_no");
		}
		if (null != begindate && !"".equals(begindate) && enddate != null && !"".equals(enddate)) {
			sql.append(" and DATE_FORMAT(sd.sd_date,'%Y-%m-%d') >=:begindate");
			sql.append(" and DATE_FORMAT(sd.sd_date,'%Y-%m-%d') <=:enddate");
		}
		if (shop_type.equals(CommonUtil.ONE) || shop_type.equals(CommonUtil.TWO)){//总部或分公司
			sql.append(" AND sp.sp_upcode = :shop_code AND (sp.sp_shop_type = '"+CommonUtil.THREE+"' OR sp.sp_shop_type = '"+CommonUtil.FIVE+"')");
		} else {//自营店,加盟店，合伙店
			sql.append(" AND t.sdl_shop_code = :shop_code");
		}
		sql.append(" and sd.sd_state ='0'");
		sql.append(" and t.companyid=:companyid");
		Integer count = namedParameterJdbcTemplate.queryForObject(sql.toString(), param, Integer.class);
		return count;
	}
	
	@Override
	public List<T_Sell_DepositList> list(Map<String, Object> param) {
		Object start = param.get("start");
		Object end = param.get("end");
		Object begindate = param.get("begindate");
		Object enddate = param.get("enddate");
		Object sdl_state = param.get("sdl_state");
		Object sdl_shop_code = param.get("sdl_shop_code");
		Object pd_no = param.get("pd_no");
		Object sdl_number = param.get("sdl_number");
//		Object shop_code = param.get(CommonUtil.SHOP_CODE);
		Object shop_type = param.get(CommonUtil.SHOP_TYPE);
		StringBuffer sql = new StringBuffer("");
		sql.append(" select sdl_id,sdl_number,sdl_pd_code,sdl_sub_code,sdl_amount,pd.pd_name AS sdl_pd_name,pd.pd_no AS sdl_pd_no,sd.sd_customer,sd.sd_tel,");
		sql.append(" sdl_ismessage,sd.sd_state,sdl_state,sp.sp_name AS sdl_sp_name,");
		sql.append("(select cr_name from t_base_color color where color.cr_code=t.sdl_cr_code and color.companyid=t.companyid LIMIT 1) as sdl_cr_name,");//颜色
		sql.append("(select sz_name from t_base_size size where size.sz_code=t.sdl_sz_code and size.companyid=t.companyid LIMIT 1) as sdl_sz_name,");//尺码
		sql.append("(select br_name from t_base_bra bra where bra.br_code=t.sdl_br_code and bra.companyid=t.companyid LIMIT 1) as sdl_br_name");//杯型
		sql.append(" from t_sell_depositlist t ");
		sql.append(" JOIN t_base_product pd ON pd.pd_code=t.sdl_pd_code AND pd.companyid=t.companyid ");
		sql.append(" JOIN t_sell_deposit sd ON sd.sd_number=t.sdl_number AND sd.companyid=t.companyid ");
		sql.append(" JOIN t_base_shop sp ON sp.sp_code=t.sdl_shop_code AND sp.companyid=t.companyid ");
		sql.append(" where 1 = 1");
		if (null != sdl_shop_code && !"".equals(sdl_shop_code)) {
			sql.append(" and  t.sdl_shop_code = :sdl_shop_code");
		}
		if (null != sdl_state && !"".equals(sdl_state)) {
			sql.append(" and  t.sdl_state = :sdl_state");
		}
		if (null != sdl_number && !"".equals(sdl_number)) {
			sql.append(" and  t.sdl_number = :sdl_number");
		}
		if (null != pd_no && !"".equals(pd_no)) {
			sql.append(" and  pd.pd_no = :pd_no");
		}
		if (null != begindate && !"".equals(begindate) && enddate != null && !"".equals(enddate)) {
			sql.append(" and DATE_FORMAT(sd.sd_date,'%Y-%m-%d') >=:begindate");
			sql.append(" and DATE_FORMAT(sd.sd_date,'%Y-%m-%d') <=:enddate");
		}
		if (shop_type.equals(CommonUtil.ONE) || shop_type.equals(CommonUtil.TWO)){//总部或分公司
			sql.append(" AND sp.sp_upcode = :shop_code AND (sp.sp_shop_type = '"+CommonUtil.THREE+"' OR sp.sp_shop_type = '"+CommonUtil.FIVE+"')");
		} else {//自营店,加盟店，合伙店
			sql.append(" AND t.sdl_shop_code = :shop_code");
		}
		sql.append(" and sd.sd_state ='0'");
		sql.append(" and t.companyid=:companyid");
		sql.append(" order by sdl_id desc");
		if(null != start && !"".equals(start) && null != end && !"".equals(end)){
			sql.append(" limit :start,:end");
		}
		List<T_Sell_DepositList> list = namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Sell_DepositList.class));
		return list;
	}
	
	@Override
	public void queryNumber(Map<String, Object> param) {
		String prefix = CommonUtil.NUMBER_PREFIX_DEPOSIT + DateUtil.getYearMonthDateYYMMDD();
		param.put("prefix", prefix);
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT CONCAT(:prefix,f_two_number(MAX(sd_number))) AS new_number");
		sql.append(" FROM t_sell_deposit");
		sql.append(" WHERE 1=1");
		sql.append(" AND INSTR(sd_number,:prefix) > 0");
		sql.append(" AND companyid=:companyid");
		String new_number = namedParameterJdbcTemplate.queryForObject(sql.toString(), 
				param, String.class);
		param.put("number", new_number);
	}

	@Override
	public void payDeposit(Map<String, Object> param,List<T_Sell_DepositList> list) {
		StringBuffer sql = new StringBuffer();
		sql.append(" INSERT INTO t_sell_depositlist(");
		sql.append(" sdl_number,sdl_pd_code,sdl_sub_code,sdl_cr_code,sdl_sz_code,sdl_br_code,");
		sql.append(" sdl_tp_code,sdl_shop_code,sdl_em_code,sdl_bd_code,sdl_main,");
		sql.append(" sdl_slave,sdl_area,sdl_vip_code,sdl_amount,sdl_sell_price,");
		sql.append(" sdl_sign_price,sdl_price,sdl_cost_price,sdl_upcost_price,");
		sql.append(" sdl_money,sdl_vip_money,sdl_hand_money,sdl_sale_money,");
		sql.append(" sdl_sale_model,sdl_sale_code,sdl_ispoint,sdl_isgift,");
		sql.append(" sdl_remark,sdl_state,companyid");
		sql.append(" ) VALUES (");
		sql.append(" :sdl_number,:sdl_pd_code,:sdl_sub_code,:sdl_cr_code,:sdl_sz_code,:sdl_br_code,");
		sql.append(" :sdl_tp_code,:sdl_shop_code,:sdl_em_code,:sdl_bd_code,:sdl_main,");
		sql.append(" :sdl_slave,:sdl_area,:sdl_vip_code,:sdl_amount,:sdl_sell_price,");
		sql.append(" :sdl_sign_price,:sdl_price,:sdl_cost_price,:sdl_upcost_price,");
		sql.append(" :sdl_money,:sdl_vip_money,:sdl_hand_money,:sdl_sale_money,");
		sql.append(" :sdl_sale_model,:sdl_sale_code,:sdl_ispoint,:sdl_isgift,");
		sql.append(" :sdl_remark,:sdl_state,:companyid");
		sql.append(")");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(list.toArray()));
		sql.setLength(0);
		param.put("sd_state", 0);
		sql.append(" INSERT INTO t_sell_deposit(");
		sql.append(" sd_number,sd_date,sd_state,sd_customer,sd_tel,sd_deposit,");
		sql.append(" sd_em_code,sd_remark,sd_shop_code,companyid");
		sql.append(" ) VALUES (");
		sql.append(" :number,:sd_date,:sd_state,:sd_customer,:sd_tel,:sd_deposit,");
		sql.append(" :emp_code,:sd_remark,:shop_code,:companyid");
		sql.append(")");
		namedParameterJdbcTemplate.update(sql.toString(), param);
	}

	@Override
	public List<T_Sell_Deposit> getDeposit(Map<String, Object> param) {
		Object begindate = param.get("begindate");
		Object code = param.get("code");
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append(" sd_id,sd_number,sd_date,sd_state,sd_customer,sd_tel,sd_deposit,");
		sql.append(" sd_em_code,");
		sql.append(" (SELECT em_name FROM t_base_emp e WHERE e.em_code=t.sd_em_code AND e.companyid=t.companyid LIMIT 1) AS emp_name");
		sql.append(" FROM t_sell_deposit t");
		sql.append(" WHERE 1=1");
		sql.append(" AND sd_shop_code=:shop_code");
		sql.append(" AND sd_state=0");
		if(!StringUtil.isEmpty(begindate)){
			sql.append(" AND sd_date between :begindate AND :enddate");
		}
		if(!StringUtil.isEmpty(code)){
			sql.append(" AND sd_tel=:code");
		}
		sql.append(" AND companyid=:companyid");
		return namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Sell_Deposit.class));
	}

	@Override
	public void take(Map<String, Object> param) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT sd_id FROM t_sell_deposit ");
		sql.append(" WHERE 1=1");
		sql.append(" AND sd_state=0");
		sql.append(" AND sd_shop_code=:shop_code");
		sql.append(" AND sd_number=:number");
		sql.append(" AND companyid=:companyid");
		Integer sd_id = null;
		try {
			sd_id = namedParameterJdbcTemplate.queryForObject(sql.toString(), param ,Integer.class);
		} catch (Exception e) {
			throw new IllegalArgumentException("可能已经被提取!");
		}
		if(null != sd_id && sd_id > 0){
			sql.setLength(0);
			sql.append(" SELECT sdl_pd_code,sdl_sub_code,sdl_cr_code,sdl_sz_code,sdl_br_code,");
			sql.append(" sdl_tp_code,sdl_shop_code,sdl_em_code,sdl_bd_code,sdl_main,");
			sql.append(" sdl_slave,sdl_area,sdl_vip_code,sdl_amount,sdl_sell_price,");
			sql.append(" sdl_sign_price,sdl_price,sdl_cost_price,sdl_upcost_price,");
			sql.append(" sdl_money,sdl_vip_money,sdl_hand_money,sdl_sale_money,");
			sql.append(" sdl_sale_model,sdl_sale_code,sdl_ispoint,sdl_isgift,");
			sql.append(" sdl_remark,sdl_state,companyid");
			sql.append(" FROM t_sell_depositlist");
			sql.append(" WHERE 1=1");
			sql.append(" AND sdl_shop_code=:shop_code");
			sql.append(" AND sdl_number=:number");
			sql.append(" AND companyid=:companyid");
			List<T_Sell_DepositList> list = namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Sell_DepositList.class));
			sql.setLength(0);
			sql.append(" INSERT INTO t_sell_shop_temp(");
			sql.append("sht_pd_code,sht_sub_code,sht_cr_code,sht_sz_code,sht_br_code,");
			sql.append("sht_tp_code,sht_shop_code,sht_em_code,sht_bd_code,sht_main,");
			sql.append("sht_slave,sht_da_code,sht_vip_code,sht_amount,sht_sell_price,");
			sql.append("sht_sign_price,sht_price,sht_cost_price,sht_upcost_price,");
			sql.append("sht_money,sht_vip_money,sht_hand_money,sht_sale_money,");
			sql.append("sht_sale_model,sht_sale_code,sht_ispoint,sht_isgift,");
			sql.append("sht_remark,sht_state,companyid");
			sql.append(" ) VALUES (");
			sql.append(":sdl_pd_code,:sdl_sub_code,:sdl_cr_code,:sdl_sz_code,:sdl_br_code,");
			sql.append(":sdl_tp_code,:sdl_shop_code,:sdl_em_code,:sdl_bd_code,:sdl_main,");
			sql.append(":sdl_slave,:sdl_area,:sdl_vip_code,:sdl_amount,:sdl_sell_price,");
			sql.append(":sdl_sign_price,:sdl_price,:sdl_cost_price,:sdl_upcost_price,");
			sql.append(":sdl_money,:sdl_vip_money,:sdl_hand_money,:sdl_sale_money,");
			sql.append(":sdl_sale_model,:sdl_sale_code,:sdl_ispoint,:sdl_isgift,");
			sql.append(":sdl_remark,0,:companyid");
			sql.append(" )");
			namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(list.toArray()));
			/*sql.setLength(0);
			sql.append(" UPDATE t_sell_deposit SET ");
			sql.append(" sd_state=1");
			sql.append(" WHERE sd_id=:sd_id");
			param.put("sd_id", sd_id);
			namedParameterJdbcTemplate.update(sql.toString(), param);*/
		}
	}

	@Override
	public void back(Map<String, Object> param) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT sd_id FROM t_sell_deposit ");
		sql.append(" WHERE 1=1");
		sql.append(" AND sd_state=0");
		sql.append(" AND sd_shop_code=:shop_code");
		sql.append(" AND sd_number=:number");
		sql.append(" AND companyid=:companyid");
		Integer sd_id = null;
		try {
			sd_id = namedParameterJdbcTemplate.queryForObject(sql.toString(), param ,Integer.class);
		} catch (Exception e) {
			throw new IllegalArgumentException("订单状态已发生变化，请刷新!");
		}
		if(null != sd_id && sd_id > 0){
			sql.setLength(0);
			sql.append(" UPDATE t_sell_deposit SET ");
			sql.append(" sd_state=2,sd_enddate=:sysdate");
			sql.append(" WHERE sd_id=:sd_id");
			param.put("sd_id", sd_id);
			param.put("sysdate", DateUtil.getCurrentTime());
			namedParameterJdbcTemplate.update(sql.toString(), param);
		}
	}

}
